﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace lcs.Web.Admin.qualitysked1
{
    public partial class importdata : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnimport_Click(object sender, EventArgs e)
        {
            string filename = string.Empty;
            try
            {
                filename = UpLoadXls(FileExcel);//上传XLS文件
                ImportXlsToData(filename);//将XLS文件的数据导入数据库               
                if (filename != string.Empty && System.IO.File.Exists(filename))
                {
                    System.IO.File.Delete(filename);//删除上传的XLS文件
                }

                Maticsoft.Common.MessageBox.Show(this, "导入成功！");
            }
            catch (Exception ex)
            {
                LblMessage.Text = ex.Message;
            }

        }
        private void ImportXlsToData(string fileName)
        {
            try
            {
                if (fileName == string.Empty)
                {
                    throw new ArgumentNullException("Excel文件上传失败！");
                }
                string oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + "; Extended Properties='Excel 8.0;IMEX=1'";
                OleDbConnection oleDBConn = null;
                OleDbDataAdapter oleAdMaster = null;
                DataTable m_tableName = new DataTable();
                DataSet ds = new DataSet();
                oleDBConn = new OleDbConnection(oleDBConnString);
                oleDBConn.Open();
                m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (m_tableName != null && m_tableName.Rows.Count > 0)
                {
                    m_tableName.TableName = "Sheet1$";
                }
                string sqlMaster;
                sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";
                oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
                oleAdMaster.Fill(ds, "m_tableName");
                oleAdMaster.Dispose();
                // oleDBConn.Close();
                oleDBConn.Dispose();
                if(ddltype.SelectedValue=="1")
                AddDatasetToSQL(ds, 12);
                else if(ddltype.SelectedValue=="2")
                    AddDatasetToSQL2(ds, 14);
                else
                    AddDatasetToSQL3(ds, 10);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
        {
            string orifilename = string.Empty;
            string uploadfilepath = string.Empty;
            string modifyfilename = string.Empty;
            string fileExtend = "";//文件扩展名
            int fileSize = 0;//文件大小
            try
            {
                if (inputfile.Value != string.Empty)
                {
                    //得到文件的大小
                    fileSize = inputfile.PostedFile.ContentLength;
                    if (fileSize == 0)
                    {
                        throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
                    }
                    //得到扩展名
                    fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    if (fileExtend.ToLower() != "xls")
                    {
                        throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
                    }
                    //路径
                    uploadfilepath = Server.MapPath("~/upload/");
                    //新文件名
                    modifyfilename = System.Guid.NewGuid().ToString();
                    //modifyfilename = inputfile.PostedFile.FileName;
                    modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    //判断是否有该目录
                    System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                    if (!dir.Exists)
                    {
                        dir.Create();
                    }
                    if (!uploadfilepath.EndsWith("\\"))
                        orifilename = uploadfilepath + "\\" + modifyfilename;
                    else
                        orifilename = uploadfilepath + modifyfilename;
                    //如果存在,删除文件
                    if (File.Exists(orifilename))
                    {
                        File.Delete(orifilename);
                    }
                    // 上传文件
                    inputfile.PostedFile.SaveAs(orifilename);
                }
                else
                {
                    throw new Exception("请选择要导入的Excel文件!");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return orifilename;
        }
        private bool AddDatasetToSQL(DataSet pds, int Cols)
        {
            lcs.BLL.qualitysked1 ms = new BLL.qualitysked1();
            lcs.Model.qualitysked1 model;
            int ic, ir;
            ic = pds.Tables[0].Columns.Count;
            if (pds.Tables[0].Columns.Count < Cols)
            {
                throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
            }
            //项目单位 工程名称 供应商名称	产品名称 产品数量 计量单位 监造单位	开工时间/计划开工时间 监造方式 生产状态	监造意见 计划日期
            ir = pds.Tables[0].Rows.Count;
            if (pds != null && pds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < pds.Tables[0].Rows.Count; i++)
                {
                    model = new Model.qualitysked1();
                    model.projectunit = pds.Tables[0].Rows[i][0].ToString().Trim();
                    if (model.projectunit.Trim() != "" && model.projectunit.Trim() != "项目单位")
                    {                        
                        model.projectname = pds.Tables[0].Rows[i][1].ToString().Trim();
                        model.supplier= pds.Tables[0].Rows[i][2].ToString().Trim();
                        model.productname = pds.Tables[0].Rows[i][3].ToString().Trim();
                        if (pds.Tables[0].Rows[i][4].ToString().Trim() != "")
                        {
                            try
                            {
                                model.productnum = decimal.Parse(pds.Tables[0].Rows[i][4].ToString().Trim());
                            }
                            catch (Exception ex)
                            {
                                model.productnum= 0;
                            }
                        }
                        model.productdw = pds.Tables[0].Rows[i][5].ToString().Trim();
                        model.engineer = pds.Tables[0].Rows[i][6].ToString().Trim();
                        if (pds.Tables[0].Rows[i][7].ToString().Trim() != "")
                            model.onstreamtime = DateTime.Parse(pds.Tables[0].Rows[i][7].ToString().Trim());                     

                        model.engineermode = pds.Tables[0].Rows[i][8].ToString().Trim();
                        model.productionstate = pds.Tables[0].Rows[i][9].ToString().Trim();
                        model.productionstate = pds.Tables[0].Rows[i][10].ToString().Trim();
                        if (pds.Tables[0].Rows[i][11].ToString().Trim() != "")
                            model.plandate = DateTime.Parse(pds.Tables[0].Rows[i][7].ToString().Trim());  
                        ms.Add(model);
                    }
                }
            }
            else
            {
                throw new Exception("导入数据为空！");
            }
            return true;
        }
        private bool AddDatasetToSQL2(DataSet pds, int Cols)
        {
            lcs.BLL.qualitysked2 ms = new BLL.qualitysked2();
            lcs.Model.qualitysked2 model;
            int ic, ir;
            ic = pds.Tables[0].Columns.Count;
            if (pds.Tables[0].Columns.Count < Cols)
            {
                throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
            }
            //抽检实施单位	工程名称	供应商	产品名称	型号规格	中标批次	中标数量	单位	抽检方式	抽检样品数量	 抽检主要内容	项目单位	抽检意见	计划日期
            ir = pds.Tables[0].Rows.Count;
            if (pds != null && pds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < pds.Tables[0].Rows.Count; i++)
                {
                    model = new Model.qualitysked2();
                    model.spotunit = pds.Tables[0].Rows[i][0].ToString().Trim();
                    if (model.spotunit.Trim() != "" && model.spotunit.Trim() != "抽检实施单位")
                    {
                        model.projectname = pds.Tables[0].Rows[i][1].ToString().Trim();
                        model.supplier = pds.Tables[0].Rows[i][2].ToString().Trim();
                        model.productname = pds.Tables[0].Rows[i][3].ToString().Trim();
                        model.ptype = pds.Tables[0].Rows[i][4].ToString().Trim();
                        model.winbatch = pds.Tables[0].Rows[i][5].ToString().Trim();
                        if (pds.Tables[0].Rows[i][6].ToString().Trim() != "")
                        {
                            try
                            {
                                model.winnum = decimal.Parse(pds.Tables[0].Rows[i][6].ToString().Trim());
                            }
                            catch (Exception ex)
                            {
                                model.winnum = 0;
                            }
                        }
                        model.productdw = pds.Tables[0].Rows[i][7].ToString().Trim();
                        model.spottype = pds.Tables[0].Rows[i][8].ToString().Trim();
                        if (pds.Tables[0].Rows[i][9].ToString().Trim() != "")
                        {
                            try
                            {
                                model.spotnum = decimal.Parse(pds.Tables[0].Rows[i][9].ToString().Trim());
                            }
                            catch (Exception ex)
                            {
                                model.spotnum = 0;
                            }
                        }
                        model.spotcontent = pds.Tables[0].Rows[i][10].ToString().Trim();
                        model.projectunit = pds.Tables[0].Rows[i][11].ToString().Trim();
                        model.spotopinion = pds.Tables[0].Rows[i][12].ToString().Trim();
                        if (pds.Tables[0].Rows[i][13].ToString().Trim() != "")
                            model.plandate = DateTime.Parse(pds.Tables[0].Rows[i][13].ToString().Trim());                        
                        ms.Add(model);
                    }
                }
            }
            else
            {
                throw new Exception("导入数据为空！");
            }
            return true;
        }
        private bool AddDatasetToSQL3(DataSet pds, int Cols)
        {
            lcs.BLL.qualitysked3 ms = new BLL.qualitysked3();
            lcs.Model.qualitysked3 model;
            int ic, ir;
            ic = pds.Tables[0].Columns.Count;
            if (pds.Tables[0].Columns.Count < Cols)
            {
                throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
            }
            //抽检实施单位	工程名称	供应商	产品名称	型号规格	中标数量	单位	项目单位	抽检意见	计划日期
            ir = pds.Tables[0].Rows.Count;
            if (pds != null && pds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < pds.Tables[0].Rows.Count; i++)
                {
                    model = new Model.qualitysked3();
                    model.spotunit = pds.Tables[0].Rows[i][0].ToString().Trim();
                    if (model.spotunit.Trim() != "" && model.spotunit.Trim() != "抽检实施单位")
                    {
                        model.projectname = pds.Tables[0].Rows[i][1].ToString().Trim();
                        model.supplier = pds.Tables[0].Rows[i][2].ToString().Trim();
                        model.productname = pds.Tables[0].Rows[i][3].ToString().Trim();
                        model.ptype = pds.Tables[0].Rows[i][4].ToString().Trim();
                        
                        if (pds.Tables[0].Rows[i][5].ToString().Trim() != "")
                        {
                            try
                            {
                                model.winnum = decimal.Parse(pds.Tables[0].Rows[i][5].ToString().Trim());
                            }
                            catch (Exception ex)
                            {
                                model.winnum = 0;
                            }
                        }
                        model.productdw = pds.Tables[0].Rows[i][6].ToString().Trim();                       
                        model.projectunit = pds.Tables[0].Rows[i][7].ToString().Trim();
                        model.spotopinion = pds.Tables[0].Rows[i][8].ToString().Trim();
                        if (pds.Tables[0].Rows[i][9].ToString().Trim() != "")
                            model.plandate = DateTime.Parse(pds.Tables[0].Rows[i][9].ToString().Trim()); 
                        ms.Add(model);
                    }
                }
            }
            else
            {
                throw new Exception("导入数据为空！");
            }
            return true;
        }
    }
}